package com.xm.util;

import com.xm.exception.SqlErrorException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


import java.sql.*;

/**对数据库中表的操作
 * @author 康文程
 * @date 2023-12-4 10 : 04
 */
public class BaseUtil {
    private static final Logger log = LoggerFactory.getLogger(BaseUtil.class);
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/mi?serverTimezone=GMT%2b8&useSSL=false&max_connections=150";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "123456";

    private Connection conn = null;
    private PreparedStatement prs = null;

    public void getConnection() {
        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            log.error("驱动没有找到，请确认是否导入驱动包.", e);
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("连接数据库失败，请确认是否数据库已经打开，或者数据库连接地址是否正确.");
        }
    }

    /**
     * 删除、新增
     * @param sql     insert into student(name,age) values(?,?)
     * @param objects ...动态参数
     * @return int 返回账号Id
     */
    public int executeUpdate(String sql, Object... objects) throws SqlErrorException {
        try {
            getConnection();
            prs = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < objects.length; i++) {
                prs.setObject(i + 1, objects[i]);
            }
            System.out.println(prs);
            prs.executeUpdate();
            ResultSet rs = prs.getGeneratedKeys();
            if (rs.next()) {
                // 返回插入的 ID
                return rs.getInt(1);
            } else {
                System.out.println("获取插入的 ID 失败");
            }
        } catch (SQLSyntaxErrorException e) {
            e.printStackTrace();
            throw new SqlErrorException("SQL语法错误", e);
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("SQL执行失败",e);
        } finally {
            this.close(null);
        }
        return 0;
    }

    /**
     * 查询、修改
     * @param sql SQL语句
     * @param objects 参数
     * @return 返回ResultSet
     */
    public ResultSet executeQuery(String sql, Object... objects) throws SqlErrorException {
        try {
            getConnection();
            prs = conn.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                prs.setObject(i + 1, objects[i]);
            }
            System.out.println(prs);
            return prs.executeQuery();
        } catch (SQLSyntaxErrorException e) {
            e.printStackTrace();
            throw new SqlErrorException("SQL语法错误", e);
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("SQL执行失败",e);
        }
        // 默认返回值，如果以上代码都没有返回结果集，则返回 null
        return null;
    }

    /**
     * 关闭资源
     * @param rs 关闭ResultSet的对象
     */
    public void close(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (prs != null) {
                prs.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("数据库关闭失败",e);
        }
    }

}
